package com.lms.dao.root;

import com.lms.bean.Root;
import com.lms.bean.User;
import com.lms.bean.UserApply;
import com.lms.dao.BaseDao;
import com.lms.bean.Role;
import com.lms.pojo.LabIntervalData;
import com.lms.pojo.RootUserApplyData;
import com.lms.service.lab.LabService;
import com.lms.service.lab.LabServiceImpl;
import com.lms.util.Constants;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class RootDaoImpl implements RootDao {
    private PreparedStatement pstm=null;
    private ResultSet rs=null;
    @Override
    public Root getLoginRoot(Connection connection, String rootCode) {
        pstm=null;
        rs=null;
        Root root=null;

        if (connection!=null){
            try {
                String sql="select * from root_account where userCode=?;";
                Object[] params={rootCode};
                rs= BaseDao.execute(connection,pstm,rs,sql,params);

                if (rs.next()){
                    root=new Root();
                    root.setUserCode(rs.getString("userCode"));
                    root.setUserName(rs.getString("userName"));
                    root.setUserPassWord(rs.getString("userPassword"));
                    root.setRootMessage(rs.getString("rootMessage"));
                    root.setRootGrade(rs.getInt("rootGrade"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                BaseDao.closeResource(connection,pstm,rs);
            }
        }

        return root;
    }
    @Override
    public int updatePwd(Connection connection, String id, String pwd) throws SQLException {
        int execute=0;
        if (connection!=null){
            String sql="update root_account set userPassword=? where userCode=?";
            PreparedStatement preparedStatement=null;
            Object[] parama={pwd,id};
            execute = BaseDao.execute(connection,preparedStatement,sql,parama);
            BaseDao.closeResource(connection,preparedStatement,null);
        }
        return execute;
    }

    @Override
    public List<User> getUserAccountList(Connection connection, String userCode, String role) throws SQLException {
        List<User> userList=new ArrayList<>();

        if (connection!=null){
            String sql="";
            Object[] params=null;
            if (!(StringUtils.isNullOrEmpty(userCode)||"null".equals(userCode))){
                sql="select * from user_account where userCode=?";
                params=new Object[]{userCode};
            }else if (!(StringUtils.isNullOrEmpty(role)||"null".equals(role))){
                sql="select * from user_account where userRole=?";
                params=new Object[]{role};
            }
            rs=BaseDao.execute(connection,pstm,rs,sql,params);
            while (rs.next()){
                User user = new User();
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassWord(rs.getString("userPassWord"));
                user.setUserRole(rs.getInt("userRole"));
                userList.add(user);
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return userList;
    }

    @Override
    public List<UserApply> getUserApplyList(Connection connection) throws SQLException {
        List<UserApply> userApplyList=new ArrayList<>();

        if (connection != null) {
            String sql = "select * from user_apply where apply_state=3 order by apply_creationDate DESC";
            Object[] params = {};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()) {
                UserApply userApply = new UserApply();
                userApply.setUserCode(rs.getString("userCode"));
                userApply.setLabCode(rs.getString("labCode"));
                userApply.setApply_applyCount(rs.getInt("apply_applyCount"));
                userApply.setApply_reason(rs.getString("apply_reason"));
                userApply.setApply_time_week(rs.getString("apply_time_week"));
                userApply.setApply_time_interval(rs.getString("apply_time_interval"));
                userApply.setApply_state(rs.getInt("apply_state"));
                userApply.setApply_creationDate(rs.getDate("apply_creationDate"));
                userApply.setApply_approvalDate(rs.getDate("apply_approvalDate"));
                userApply.setApply_rootId(rs.getString("apply_rootId"));
                userApplyList.add(userApply);
            }
            BaseDao.closeResource(connection, pstm, rs);
        }
        return userApplyList;

    }

    @Override
    public List<RootUserApplyData> getRootUserApplyDataList(Connection connection) throws SQLException {
        List<RootUserApplyData> rootUserApplyDataList = new ArrayList<>();

        if (connection!=null){
            String sql = "select applyID, ap.userCode,userName,userRole,labName,apply_time_week,apply_time_interval,apply_reason,apply_creationDate"+
            " from user_apply as ap inner join user_account  as ac on ap.userCode=ac.userCode"+
            " inner join lab_data ld on ap.labCode = ld.labCode"+
            " where apply_state=3 order by apply_creationDate DESC";
            Object[] params={};
            rs=BaseDao.execute(connection,pstm,rs,sql,params);

            while (rs.next()){
                RootUserApplyData rootUserApplyData = new RootUserApplyData();
                rootUserApplyData.setApplyID(rs.getInt("applyID"));
                rootUserApplyData.setUserName(rs.getString("userName"));
                rootUserApplyData.setUserCode(rs.getString("userCode"));
                if (rs.getInt("userRole")==0){
                    rootUserApplyData.setUserRole("学生");
                }else {
                    rootUserApplyData.setUserRole("教师");
                }
                rootUserApplyData.setLabName(rs.getString("labName"));
                rootUserApplyData.setApply_creationDate(new SimpleDateFormat("yyyy.MM.dd").format(rs.getDate("apply_creationDate")));
                rootUserApplyData.setApply_reason(rs.getString("apply_reason"));
                String res="";
                switch (rs.getString("apply_time_week")){
                    case "mon":
                        res += "周一";
                        break;
                    case "tue":
                        res += "周二";
                        break;
                    case "wed":
                        res += "周三";
                        break;
                    case "thur":
                        res += "周四";
                        break;
                    case "fri":
                        res += "周五";
                        break;
                    case "sat":
                        res += "周六";
                        break;
                    case "sun":
                        res += "周天";
                        break;

                }
                switch (rs.getString("apply_time_interval")){
                    case "one":res+="1-2";break;
                    case "two":res+="3-4";break;
                    case "three":res+="5-6";break;
                    case "four":res+="7-8";break;
                    case "five":res+="9-10";break;
                }
                rootUserApplyData.setInterval(res);
                rootUserApplyDataList.add(rootUserApplyData);
            }
            BaseDao.closeResource(connection, pstm, rs);
        }
        return rootUserApplyDataList;
    }

    @Override
    public int setOpenState(Connection connection,String labCode, String week, String interval, String state) throws SQLException {
        int res=0;
        if (connection!=null){
            String sql="update lab_"+week+" set "+interval+"=? where labCode=?";
            Object[] params={state,labCode};
            res=BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(connection, pstm, rs);
        }
        return res;
    }

    @Override
    public int refuse(Connection connection, String rootID,String applyID) throws SQLException {
        int res=0;
        if (connection!=null){
            String sql="update user_apply set apply_state=2,apply_rootID=?,apply_approvalDate=? where applyID=?";
            Object[] params={rootID,new SimpleDateFormat("yyyy-MM-dd").format(new Date()),applyID};
            res=BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(connection, pstm, rs);
        }
        return res;
    }

    @Override
    public int agree(Connection connection,String rootID, String applyID) throws SQLException {
        int res=0;
        if (connection!=null){
            String sql="update user_apply set apply_state=1,apply_rootID=?,apply_approvalDate=? where applyID=?";
            Object[] params={rootID,new SimpleDateFormat("yyyy-MM-dd").format(new Date()),applyID};
            res=BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(connection, pstm, rs);
        }
        return res;
    }

    @Override
    public int reset(Connection connection, String userCode) throws SQLException {
        int res=0;
        if (connection!=null){
            String sql="update user_account set userPassword='"+ Constants.RESET_PWD +"' where userCode=?";
            Object[] params={userCode};
            res=BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(connection, pstm, rs);
        }
        return res;
    }

    @Override
    public int delete(Connection connection, String userCode) throws SQLException {
        int res=0;
        if (connection!=null){
            String sql="delete from user_account where userCode=?";
            Object[] params={userCode};
            res=BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(connection, pstm, rs);
        }
        return res;
    }

    @Override
    public int setRootMsg(Connection connection,String userCode, String rootMsg) throws SQLException {
        int res=0;
        if (connection!=null){
            String sql="update root_account set rootMessage=? where userCode=?";
            Object[] params={rootMsg,userCode};
            res=BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(connection, pstm, rs);
        }
        return res;
    }

    @Override
    public String getRootOpenID(Connection connection) throws SQLException {
        if (connection!=null){
            String sql="select openID from root_account where rootGrade=1";
            rs = BaseDao.execute(connection, pstm, rs, sql, new Object[]{});
            while (rs.next()){
                return rs.getString("openID");
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return null;
    }
}
